<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xml:lang="en" lang="en" xmlns="http://www.w3.org/1999/xhtml">
  <head>
    <meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
    <meta http-equiv="Content-Style-Type" content="text/css" />
    <!-- MOTW-DISABLED saved from url=(0014)about:internet -->
    <title>Display only customers that have orders or&nbsp;payments</title>
    <link rel="StyleSheet" href="css/subreport.css" type="text/css" media="all" />
    <link rel="StyleSheet" href="css/webworks.css" type="text/css" media="all" />
    <link rel="StyleSheet" href="webworks.css" type="text/css" media="all" />
    <script type="text/javascript" language="JavaScript1.2">
      <!--
        var  WebWorksRootPath = "";
      // -->
    </script>
  </head>
  <body>
    <div style="text-align: left;">
      <table cellspacing="0" summary="">
        <tr>
          <td>
            <a href="sub-TutT10LinkPaymentsSubrpttoMasterRpt.html"><img src="images/prev.gif" alt="Previous" border="0" /></a>
          </td>
          <td>
            <a href="sub-TutT12DisplaySubrptsNextToOneAnother.html"><img src="images/next.gif" alt="Next" border="0" /></a>
          </td>
        </tr>
      </table>
    </div>
    <hr align="left" />
    <blockquote>
      <h3 class="N_TutorialTask">Task 11:&nbsp;&nbsp;<a name="187880">Display only customers that have orders or&nbsp;payments</a></h3>
      <p class="b_Body"><a name="215418">The database contains customers that do not have orders or payments. The query for the customers report returns all customers. When you run the report, there are customer rows that show only the column headings for the Orders and Payments tables, as shown in </a><a href="#227184" title="Display only customers that have orders or payments">Figure&nbsp;12-23</a>.</p>
      <p class="i1_Image1"><a name="215391"><img class="Default" src="images/nosubreports.png" style="display: inline; float: none; left: 0.0; top: 0.0" alt="Figure 12-23 Report showing no order or payment data for one customer" /></a></p>
      <div class="fc_FigCalloutTitle">
        <b class="Bold">Figure&nbsp;12-23&nbsp;&nbsp;</b><a name="227184">Report showing no order or payment data for one customer</a></div>
      <p class="b_Body"><a name="187889">You can exclude customers that do not have orders or payments by changing the query for the customers report.</a></p>
      <div class="N_n1_NumList1_outer" style="margin-left: 0pt">
        <table border="0" cellspacing="0" cellpadding="0" summary="" role="presentation">
          <tr style="vertical-align: baseline">
            <td>
              <div class="N_n1_NumList1_inner" style="width: 14.1732pt; white-space: nowrap">
                <span class="zAuto_0023Format">1&nbsp;&nbsp;</span>
              </div>
            </td>
            <td width="100%">
              <div class="N_n1_NumList1_inner"><a name="187890">Choose Layout to resume editing the report.</a></div>
            </td>
          </tr>
        </table>
      </div>
      <div class="N_n1_NumList1_outer" style="margin-left: 0pt">
        <table border="0" cellspacing="0" cellpadding="0" summary="" role="presentation">
          <tr style="vertical-align: baseline">
            <td>
              <div class="N_n1_NumList1_inner" style="width: 14.1732pt; white-space: nowrap">
                <span class="zAuto_0023Format">2&nbsp;&nbsp;</span>
              </div>
            </td>
            <td width="100%">
              <div class="N_n1_NumList1_inner"><a name="187891">In Data Explorer, expand Data Sets, right-click Customers, then choose Edit.</a></div>
            </td>
          </tr>
        </table>
      </div>
      <div class="N_n1_NumList1_outer" style="margin-left: 0pt">
        <table border="0" cellspacing="0" cellpadding="0" summary="" role="presentation">
          <tr style="vertical-align: baseline">
            <td>
              <div class="N_n1_NumList1_inner" style="width: 14.1732pt; white-space: nowrap">
                <span class="zAuto_0023Format">3&nbsp;&nbsp;</span>
              </div>
            </td>
            <td width="100%">
              <div class="N_n1_NumList1_inner"><a name="292688">Add the following SQL lines to the end of the existing query:</a></div>
            </td>
          </tr>
        </table>
      </div>
      <div class="cf_CodeFirst"><a name="292689">&nbsp;&nbsp;WHERE </a></div>
      <div class="co_Code"><a name="292690">&nbsp;&nbsp;EXISTS </a></div>
      <div class="co_Code"><a name="292691">&nbsp;&nbsp;(SELECT Orders.customerNumber</a></div>
      <div class="co_Code"><a name="292692">&nbsp;&nbsp;FROM Orders </a></div>
      <div class="co_Code"><a name="292693">&nbsp;&nbsp;WHERE Customers.customerNumber = </a></div>
      <div class="co_Code"><a name="292694">&nbsp;&nbsp;Orders.customerNumber)</a></div>
      <div class="co_Code"><a name="292695">&nbsp;&nbsp;OR</a></div>
      <div class="co_Code"><a name="292696">&nbsp;&nbsp;EXISTS </a></div>
      <div class="cl_CodeLong"><a name="292697">&nbsp;&nbsp;(SELECT Payments.customerNumber</a></div>
      <div class="co_Code"><a name="292698">&nbsp;&nbsp;FROM Payments </a></div>
      <div class="co_Code"><a name="292699">&nbsp;&nbsp;WHERE Customers.customerNumber = </a></div>
      <div class="co_Code"><a name="292700">&nbsp;&nbsp;Payments.customerNumber)</a></div>
      <div class="N_i_Indent1"><a name="292701">The WHERE EXISTS clause checks the Orders and Payments tables for customerNumber values that match the customerNumber values in the Customers table. Only rows that have matching customerNumber values are selected. The complete query should look like the one shown in </a><a href="#227192" title="Display only customers that have orders or payments">Figure&nbsp;12-24</a>.</div>
      <p class="i2_Image2"><a name="187911"><img class="Default" src="images/newsql.png" style="display: inline; float: none; left: 0.0; top: 0.0" alt="Figure 12-24 Updated SELECT query in Edit Data Set" /></a></p>
      <div class="fc2_FigCall2Title">
        <b class="Bold">Figure&nbsp;12-24&nbsp;&nbsp;</b><a name="227192">Updated SELECT query in Edit Data Set</a></div>
      <div class="N_n1_NumList1_outer" style="margin-left: 0pt">
        <table border="0" cellspacing="0" cellpadding="0" summary="" role="presentation">
          <tr style="vertical-align: baseline">
            <td>
              <div class="N_n1_NumList1_inner" style="width: 14.1732pt; white-space: nowrap">
                <span class="zAuto_0023Format">4&nbsp;&nbsp;</span>
              </div>
            </td>
            <td width="100%">
              <div class="N_n1_NumList1_inner"><a name="187913">Choose Preview Results to verify that the query returns rows, then choose OK to save the change to the data set.</a></div>
            </td>
          </tr>
        </table>
      </div>
      <div class="N_n1_NumList1_outer" style="margin-left: 0pt">
        <table border="0" cellspacing="0" cellpadding="0" summary="" role="presentation">
          <tr style="vertical-align: baseline">
            <td>
              <div class="N_n1_NumList1_inner" style="width: 14.1732pt; white-space: nowrap">
                <span class="zAuto_0023Format">5&nbsp;&nbsp;</span>
              </div>
            </td>
            <td width="100%">
              <div class="N_n1_NumList1_inner"><a name="187914">Preview the report. Scroll down the report to check the output. The report no longer displays customers that do not have orders or payments.</a></div>
            </td>
          </tr>
        </table>
      </div>
    </blockquote>
    <hr align="left" />
    <table align="right" summary="">
      <tr>
        <td class="WebWorks_Company_Name_Bottom">
          <a href="notices.html">(c) Copyright Actuate Corporation 2013</a>
        </td>
      </tr>
    </table>
  </body>
</html>